package com.pj.lib.auraClient.content;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBAdapter 
{
	private static final String TAG = "DBAdapter";
	
	private static final String DATABASE_NAME = "lib_aura.db";
    private static final int DATABASE_VERSION = 1;
    
	public interface Rooms {
		public final static String NAME = "rooms";
		/**
		 * code of room. primary key
		 * <p>VARCHAR(20)</p>
		 */
		public final static String KEY_CODE = "code";
		/**
		 * name of room
		 * <p>VARCHAR(50)</p>
		 */
		public final static String KEY_NAME = "name";
		/**
		 * what aura is enabled. if not it is null
		 */
		public final static String KEY_AURA = "aura";
		
		public static final String TABLE_CREATE =
	        "create table rooms (code varchar(20) primary key, "
	        + "name varchar(50), aura varchar(20));";
	}
	public interface AuraTypes {
		public final static String NAME = "aura_types";
		/**
		 * code of aura type. primary key
		 * <p>VARCHAR(20)</p>
		 */
		public final static String KEY_CODE = "code";
		/**
		 * Foreign key to rooms code.
		 * <p>VARCHAR(20)</p>
		 */
		public final static String KEY_ROOM_CODE = "room_code";
		/**
		 * Name of aura. Can be null
		 * <p>VARCHAR(50)</p>
		 */
		public final static String KEY_NAME = "name";
		/**
		 * Radius of aura
		 * <p>INTEGER</p>
		 */
		public final static String KEY_RADIUS = "radius";
		
		public static final String TABLE_CREATE =
	        "create table aura_types (code varchar(20) primary key, "
	        + "room_code varchar(20) not null, name varchar(50), radius INTEGER);";
	}

	public interface Users {
		public final static String NAME = "users";
		public final static String KEY_ROW_ID = "_id";
		/**
		 * Id of user from server
		 * <p>INTEGER</p>
		 */
		public final static String KEY_USER_ID = "user_id";
		/**
		 * Name of user
		 * <p>VARCHAR(50)</p>
		 */
		public final static String KEY_NAME = "user_name";
		
		public static final String TABLE_CREATE =
	        "create table users (_id integer primary key, "
	        + "user_id integer not null unique, name varchar(50));";
	}
	
	
	public interface UserRoom {
		public final static String NAME = "user_room";
		public final static String KEY_ROW_ID = "_id";
		/**
		 * user id from server
		 * <p>INTEGER</p>
		 */
		public final static String KEY_USER_ID = "user_id";
		/**
		 * room code. foreign key to rooms
		 * <p>VARCHAR(20)</p>
		 */
		public final static String KEY_ROOM_CODE = "room_code";
		/**
		 * state beetwen this user to you (you are aura caster). Can be: NO_AURA, OUT_OF_AURA, IN_AURA, GOING_OUT_OF_AURA
		 * <p>VARCHAR(20)</p>
		 */
		public final static String KEY_USER_STATE = "user_state";
		/**
		 * state beetwen this user to you (he is aura caster). Can be: NO_AURA, OUT_OF_AURA, IN_AURA, GOING_OUT_OF_AURA
		 * <p>VARCHAR(20)</p>
		 */
		public final static String KEY_YOUR_STATE = "your_state";
		
		public static final String TABLE_CREATE =
	        "create table users (_id integer primary key, "
	        + "user_id integer not null, room_code varchar(20) not null," +
	        		" user_state varchar(20) DEFAULT 'NO_AURA', your_state varchar(20) DEFAULT 'NO_AURA');";
	}
	
  
    
    
    

    
        
    private final Context context; 
    
    private DatabaseHelper DBHelper;
    private SQLiteDatabase db;

    public DBAdapter(Context ctx) 
    {
        this.context = ctx;
        DBHelper = new DatabaseHelper(context);
    }
        
    private static class DatabaseHelper extends SQLiteOpenHelper 
    {
        DatabaseHelper(Context context) 
        {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }

        @Override
        public void onCreate(SQLiteDatabase db) 
        {
            db.execSQL(Rooms.TABLE_CREATE);
            db.execSQL(Users.TABLE_CREATE);
            db.execSQL(AuraTypes.TABLE_CREATE);
            db.execSQL(UserRoom.TABLE_CREATE);
        }

        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, 
        int newVersion) 
        {
            Log.w(TAG, "Upgrading database from version " + oldVersion 
                    + " to "
                    + newVersion + ", which will destroy all old data");
            db.execSQL("DROP TABLE IF EXISTS " + Rooms.NAME);
            db.execSQL("DROP TABLE IF EXISTS " + Users.NAME);
            db.execSQL("DROP TABLE IF EXISTS " + AuraTypes.NAME);
            db.execSQL("DROP TABLE IF EXISTS " + UserRoom.NAME);
            onCreate(db);
        }
    }    
    
    //---opens the database---
    public DBAdapter open() throws SQLException 
    {
        db = DBHelper.getWritableDatabase();
        return this;
    }

    //---closes the database---    
    public void close() 
    {
        DBHelper.close();
    }
    
    public long insertRoom(String code, String name, String aura) {
    	ContentValues initialValues = new ContentValues();
        initialValues.put(Rooms.KEY_CODE, code);
        initialValues.put(Rooms.KEY_NAME, name);
        initialValues.put(Rooms.KEY_AURA, aura);
        return db.insert(Rooms.NAME, null, initialValues);
    }
    public long insertAuraType(String code, String roomCode, String name, int radius) {
    	ContentValues initialValues = new ContentValues();
        initialValues.put(AuraTypes.KEY_CODE, code);
        initialValues.put(AuraTypes.KEY_NAME, name);
        initialValues.put(AuraTypes.KEY_ROOM_CODE, roomCode);
        initialValues.put(AuraTypes.KEY_RADIUS, radius);
        return db.insert(AuraTypes.NAME, null, initialValues);
    }
    public long insertUser(int userId, String name) {
    	ContentValues initialValues = new ContentValues();
        initialValues.put(Users.KEY_NAME, name);
        initialValues.put(Users.KEY_USER_ID, userId);
        return db.insert(Users.NAME, null, initialValues);
    }
    public long insertUserRoom(int userId, String roomCode, String userState, String yourState) {
    	ContentValues initialValues = new ContentValues();
        initialValues.put(UserRoom.KEY_USER_ID, userId);
        initialValues.put(UserRoom.KEY_ROOM_CODE, roomCode);
        if(userState != null)
        	initialValues.put(UserRoom.KEY_USER_STATE, userState);
        if(yourState != null)
        	initialValues.put(UserRoom.KEY_YOUR_STATE, yourState);
        return db.insert(UserRoom.NAME, null, initialValues);
    }
    
    
    // delete
    public long deleteRoom(String code) {
    	return db.delete(Rooms.NAME, Rooms.KEY_CODE + " = ?", new String[] {code});
    }

    public long deleteAuraType(String code) {
    	return db.delete(AuraTypes.NAME, AuraTypes.KEY_CODE + " = ?", new String[] {code});
    }
    public long deleteUser(int userId) {
    	return db.delete(Users.NAME, Users.KEY_USER_ID+ " = " + userId, null);
    }
    public long deleteUserRoom(int rowId) {
    	return db.delete(UserRoom.KEY_ROW_ID, UserRoom.KEY_ROW_ID+ " = " + rowId, null);
    }

    private Cursor getQuery(String table, String selection, String[] selectionArgs, String orderBy) {
    	return db.query(table, null, selection, selectionArgs, null, null, orderBy);
    }
    
    // query
    public Cursor getRooms(String selection, String[] selectionArgs, String orderBy) {
    	return getQuery(Rooms.NAME, selection, selectionArgs, orderBy);
    }
    public Cursor getAuraTypes(String selection, String[] selectionArgs, String orderBy) {
    	return getQuery(AuraTypes.NAME, selection, selectionArgs, orderBy);
    }
    public Cursor getUsers(String selection, String[] selectionArgs, String orderBy) {
    	return getQuery(Users.NAME, selection, selectionArgs, orderBy);
    }
    public Cursor getUserRoom(String selection, String[] selectionArgs, String orderBy) {
    	return getQuery(UserRoom.NAME, selection, selectionArgs, orderBy);
    }

    // update
    public boolean updateRoomAura(String roomCode, String aura) {
    	ContentValues args = new ContentValues();
    	args.put(Rooms.KEY_AURA, aura);
    	return db.update(Rooms.NAME, args, Rooms.KEY_CODE + " = ?", new String[] {roomCode}) >0;
    }
    public boolean updateUserName(int userId, String name) {
    	ContentValues args = new ContentValues();
    	args.put(Users.KEY_NAME, name);
    	return db.update(Users.NAME, args, Users.KEY_USER_ID + " = " + userId, null) >0;
    }
    
    public boolean updateUserRoom(int userId, String roomCode, String yourState, String userState) {
    	ContentValues args = new ContentValues();
    	if(yourState != null) {
    		args.put(UserRoom.KEY_YOUR_STATE, yourState);
    	}
    	if(userState != null) {
    		args.put(UserRoom.KEY_USER_STATE, userState);
    	}
    	
    	return db.update(UserRoom.NAME, args, UserRoom.KEY_USER_ID + " = " + userId + " and " + UserRoom.KEY_ROOM_CODE + " = ?", new String[] {roomCode})  >0;
    }

}

